Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Using a temp-table to summarize data

The beginning of this chapter notes two basic purposes for temp-tables: first, to let you define a table unlike any single database table for data summary or other uses; and second, to let you pass a set of data as a parameter between procedures. In this section, you’ll work through an example of the first kind. You’ll write a procedure that defines a temp-table and uses it to total invoice amounts for each Customer, and at the same time to count the number of Invoices for each Customer and identify which one has the highest amount. The finished procedure is saved as h-InvSummary.p.

In addition to the Customer table you’re familiar with, the example uses the Invoice table in the Sports2000 database. The Invoice table holds information for each Invoice a Customer has been sent for each Order. It has a join to the Customer table and to the Order table, along with the date and amount of the Invoice and other information.

First is the statement to define the temp-table itself:

/* Procedure h-InvSummary.p -- uses a temp-table to build a summary report of invoices by customer. */ DEFINE TEMP-TABLE ttInvoice   FIELD iCustNum    LIKE Invoice.CustNum  LABEL "Cust#"  FORMAT "ZZ9"   FIELD cCustName   LIKE Customer.NAME                   FORMAT "X(20)"   FIELD iNumInvs    AS INTEGER     LABEL "# Inv's"       FORMAT "Z9"   FIELD dInvTotal   AS DECIMAL     LABEL "Inv Total  "   FORMAT ">>,>>9.99"   FIELD dMaxAmount  AS DECIMAL     LABEL "Max Amount   " FORMAT ">>,>>9.99"   FIELD iInvNum     LIKE Invoice.InvoiceNum LABEL "Inv#" FORMAT "ZZ9"   INDEX idxCustNum IS PRIMARY iCustNum   INDEX idxInvTotal dInvTotal.

The procedure creates one record in the temp-table for each Customer, summarizing its Invoices. As you can see, the temp-table has these fields:

A Customer Number field derived from that field in the Invoice table.
A Customer Name field derived from that field in the Customer table. Later you’ll use the Customer Number to retrieve the Customer record so that you can add the name to the invoice information.
A count of the number of Invoices for the Customer.
A total of the Invoices for the Customer.
The amount of the largest Invoice for the Customer.
The number of the Invoice with the largest amount for the Customer.

The field definitions define or override the field label and default format in some cases, using phrases attached to the FIELD definition. By default, the right-justified label for a numeric field extends somewhat to the right of the data, which in the case of the InvTotal and MaxAmount fields doesn’t look quite right, so the extra spaces in their labels correct that.

The temp-table also has two indexes. The first orders the records by Customer Number. This index is useful because the code finds records based on that value to accumulate the Invoice total and other values. This is the primary index for the temp-table, so if you display or otherwise iterate through the temp-table records without any other specific sort, they appear in Customer Number order.

The second index is by the Invoice Total. This index is useful because the procedure uses it as the sort order for the final display of all the records.

The first executable code begins a FOR EACH block that joins each Invoice record to its Customer record. The OF phrase uses the CustNum field that the two tables have in common to join them. The FIND statement checks to see whether there is already a temp-table record for the Customer. If there isn’t, it uses the CREATE statement to create one. You’ll learn a lot more about the CREATE statement in Chapter 16, " Updating Your Database and Writing Triggers." For now you just need to know that this statement creates a new record either in a database table or, as you see here, in a temp-table. That new record holds the initial values of the fields in the table until you set them to other values.

After the new record is created, the code sets the key value (the iCustNum field) and saves off the Customer Name from that table. The ASSIGN statement lets you make multiple field assignments at once and is more efficient than a series of statements that do one field assignment each:

/* Retrieve each invoice along with its Customer record, to get the Name. */ FOR EACH Invoice, Customer OF Invoice:     FIND FIRST ttInvoice WHERE ttInvoice.iCustNum = Invoice.CustNum NO-ERROR.     /* If there isn't already a temp-table record for the Customer,     create it and save the Customer # and Name. */     IF NOT AVAILABLE ttInvoice THEN     DO:         CREATE ttInvoice.         ASSIGN ttInvoice.iCustNum = Invoice.CustNum                ttInvoice.cCustName = Customer.NAME.     END.

Next, the code compares the Amount of the current Invoice with the dMaxAmount field in the temp-table record (which is initially 0 for each newly created record). If the current Amount is larger than that value, it’s replaced with the new Amount and the Invoice number is saved off in the iInvNum field. In this way, the temp-table records wind up holding the highest Invoice Amount for the Customer after it has cycled through all the Invoices:

/* Save off the Invoice amount if it's a new high for this Customer. */     IF Invoice.Amount > dMaxAmount THEN         ASSIGN dMaxAmount = Invoice.Amount                iInvNum = Invoice.InvoiceNum.

Still in the FOR EACH loop, the code next increments the Invoice total for the Customer and the count of the number of Invoices for the Customer:

/* Increment the Invoice total and Invoice count for the Customer. */     ASSIGN ttInvoice.dInvTotal = ttInvoice.dInvTotal +                 Invoice.Amount            ttInvoice.iNumInvs = ttInvoice.iNumInvs + 1. END.    /* END FOR EACH Invoice & Customer */

Now the procedure has finished cycling through all of the Invoices, and it can take the summary data in the temp-table and display it, in this case with the Customer with the highest Invoice Total first:

/* Now display the results in descending order by invoice total. */ FOR EACH ttInvoice BY dInvTotal DESC:     DISPLAY iCustNum cCustName iNumInvs dInvTotal iInvNum dMaxAmount. END.

Figure 11–1 shows the first page of the output report you should see when you run the procedure.

Figure 11–1: First page result of h-InvSummary.p

Using the temp-table made it easy to accumulate different kinds of summary data and to combine information from different database tables together in a single report. You could easily display this data in different ways, for example sorted by different fields, without having to again retrieve it from the database.